<?php
//=======================================
//###################################
// Kayako Web Solutions
//
// Source Copyright 2001-2004 Kayako Infotech Ltd.
// Unauthorized reproduction is not allowed
// License Number: $%LICENSE%$
// $Author: vshoor $ ($Date: 2006/12/18 19:11:27 $)
// $RCSfile: setup.php,v $ : $Revision: 1.7 $ 
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//
//###################################
//=======================================

if (!defined("INSWIFT")) {
	trigger_error("Unable to process $PHP_SELF", E_USER_ERROR);
}


/**
* Called when the module is to be installed
*/
function _livesupport_install($action, $page = '') {
	global $_SWIFT;

	if ($action == "getpages")
	{
		return "3";
	} else if ($action == "buildpage") {

		switch ($page)
		{
			case '1':
				$_SWIFT["query"]["create"]["visitorfootprints"] = "CREATE TABLE ". TABLE_PREFIX ."visitorfootprints(
																				sessionid VARCHAR(32) NOT NULL DEFAULT '',
																				pageurl VARCHAR(255) NOT NULL DEFAULT '',
																				pagehash VARCHAR(32) NOT NULL DEFAULT '',
																				pagetitle VARCHAR(255) NOT NULL DEFAULT '',
																				country VARCHAR(255) NOT NULL DEFAULT '',
																				countrycode VARCHAR(2) NOT NULL DEFAULT '',
																				
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				lastactivity INT UNSIGNED NOT NULL DEFAULT '0',
																				ipaddress VARCHAR(80) NOT NULL DEFAULT '0.0.0.0',
																				hostname VARCHAR(80) NOT NULL DEFAULT '',
																				referrer VARCHAR(255) NOT NULL DEFAULT '',
																				resolution VARCHAR(30) NOT NULL DEFAULT '',
																				colordepth VARCHAR(20) NOT NULL DEFAULT '',
																				appversion VARCHAR(150) NOT NULL DEFAULT '',
																				operatingsys VARCHAR(20) NOT NULL DEFAULT '',
																				browsername VARCHAR(150) NOT NULL DEFAULT '',
																				browserversion VARCHAR(150) NOT NULL DEFAULT '',
																				browsercode VARCHAR(2) NOT NULL DEFAULT '',
																				searchenginename VARCHAR(20) NOT NULL DEFAULT '',
																				searchstring VARCHAR(255) NOT NULL DEFAULT '',
																				searchengineurl VARCHAR(200) NOT NULL DEFAULT '',
																				platform VARCHAR(150) NOT NULL DEFAULT '',
																				rowbgcolor VARCHAR(7) NOT NULL DEFAULT '',
																				rowfrcolor VARCHAR(7) NOT NULL DEFAULT '',
																				hasnote SMALLINT UNSIGNED NOT NULL DEFAULT '0',
																				topull SMALLINT UNSIGNED NOT NULL DEFAULT '0',
																				campaignid INT UNSIGNED NOT NULL DEFAULT '0',
																				campaigntitle VARCHAR(255) NOT NULL DEFAULT '',
																				KEY sessionid (sessionid, pagehash),
																				KEY lastactivity (lastactivity)
																			);";

				$_SWIFT["query"]["create"]["visitorpulls"] = "CREATE TABLE ". TABLE_PREFIX ."visitorpulls(
																				visitorsessionid VARCHAR(32) NOT NULL DEFAULT '',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				PRIMARY KEY (visitorsessionid),
																				KEY visitorpulls1 (staffid)
																			);";
																		
				$_SWIFT["query"]["create"]["countryinfo"] = "CREATE TABLE ". TABLE_PREFIX ."countryinfo (
																				ip_from DOUBLE NOT NULL DEFAULT '0',
																				ip_to DOUBLE NOT NULL DEFAULT '0',
																				country_code2 CHAR(2) NOT NULL DEFAULT '',
																				country_code3 CHAR(3) NOT NULL DEFAULT '',
																				country_name VARCHAR(50) NOT NULL DEFAULT '',
																				KEY ips (ip_from, ip_to)
																			);";
																			
				$_SWIFT["query"]["create"]["chatobjects"] = "CREATE TABLE ". TABLE_PREFIX ."chatobjects (
																				chatobjectid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				visitorsessionid VARCHAR(32) NOT NULL DEFAULT '',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				lastpostactivity INT UNSIGNED NOT NULL DEFAULT '0',
																				userpostactivity INT UNSIGNED NOT NULL DEFAULT '0',
																				staffpostactivity INT UNSIGNED NOT NULL DEFAULT '0',
																				userid INT UNSIGNED NOT NULL DEFAULT '0',
																				userfullname VARCHAR(150) NOT NULL DEFAULT '',
																				useremail VARCHAR(255) NOT NULL DEFAULT '',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				staffname VARCHAR(150) NOT NULL DEFAULT '',
																				chatstatus SMALLINT UNSIGNED NOT NULL DEFAULT '0',
																				transferfromid INT UNSIGNED NOT NULL DEFAULT '0',
																				transfertoid INT UNSIGNED NOT NULL DEFAULT '0',
																				transferstatus SMALLINT(1) UNSIGNED NOT NULL DEFAULT '0',
																				transfertimeline INT UNSIGNED NOT NULL DEFAULT '0',
																				roundrobintimeline INT UNSIGNED NOT NULL DEFAULT '0',
																				roundrobinhits INT UNSIGNED NOT NULL DEFAULT '0',
																				departmentid INT UNSIGNED NOT NULL DEFAULT '0',
																				departmenttitle VARCHAR(100) NOT NULL DEFAULT '',
																				chattype SMALLINT UNSIGNED NOT NULL DEFAULT '0',
																				ipaddress VARCHAR(50) NOT NULL DEFAULT '',
																				isproactive SMALLINT(1) UNSIGNED NOT NULL DEFAULT '0',
																				PRIMARY KEY (chatobjectid),
																				KEY chatobjects1 (chatstatus, staffid, lastpostactivity),
																				KEY chatobjects2 (chatstatus, chattype),
																				KEY chatobjects3 (visitorsessionid),
																				KEY chatobjects4 (staffid),
																				KEY chatobjects5 (userid),
																				KEY chatobjects6 (ipaddress),
																				KEY chatobjects7 (departmentid, dateline),
																				KEY chatobjects8 (useremail),
																				KEY chatobjects9 (chatstatus, staffid, dateline)
																			);";
																			
				$_SWIFT["query"]["create"]["chatdata"] = "CREATE TABLE ". TABLE_PREFIX ."chatdata (
																				chatdataid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				chatobjectid INT UNSIGNED NOT NULL DEFAULT '0',
																				contents MEDIUMTEXT,
																				PRIMARY KEY (chatdataid),
																				KEY cobjid (chatobjectid)
																			);";

																			
				$_SWIFT["query"]["create"]["chatchilds"] = "CREATE TABLE ". TABLE_PREFIX ."chatchilds (
																				chatchildid VARCHAR(32) NOT NULL DEFAULT '',
																				chatobjectid INT UNSIGNED NOT NULL DEFAULT '0',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				PRIMARY KEY (chatchildid),
																				KEY staffid (chatobjectid, staffid)
																			);";
																			
				$_SWIFT["query"]["create"]["messagequeue"] = "CREATE TABLE ". TABLE_PREFIX ."messagequeue (
																				messagequeueid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				chatobjectid INT UNSIGNED NOT NULL DEFAULT '0',
																				chatchildid VARCHAR(32) NOT NULL DEFAULT '',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				name VARCHAR(150) NOT NULL DEFAULT '',
																				contents MEDIUMTEXT,
																				msgtype VARCHAR(10) NOT NULL DEFAULT '',
																				PRIMARY KEY (messagequeueid),
																				KEY messagequeue1 (chatobjectid, chatchildid),
																				KEY messagequeue2 (dateline)
																			);";

																			
				$_SWIFT["query"]["create"]["messages"] = "CREATE TABLE ". TABLE_PREFIX ."messages (
																				messageid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				replydateline INT UNSIGNED NOT NULL DEFAULT '0',
																				fullname VARCHAR(150) NOT NULL DEFAULT '',
																				email VARCHAR(150) NOT NULL DEFAULT '',
																				subject VARCHAR(255) NOT NULL DEFAULT '',
																				departmentid INT UNSIGNED NOT NULL DEFAULT '0',
																				messagestatus SMALLINT(1) UNSIGNED NOT NULL DEFAULT '0',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				PRIMARY KEY (messageid),
																				KEY messages1 (departmentid, messagestatus),
																				KEY messages2 (staffid)
																			);";

																			
				$_SWIFT["query"]["create"]["messagedata"] = "CREATE TABLE ". TABLE_PREFIX ."messagedata (
																				messagedataid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				messageid INT UNSIGNED NOT NULL DEFAULT '0',
																				contenttype SMALLINT(1) UNSIGNED NOT NULL DEFAULT '0',
																				contents MEDIUMTEXT,
																				PRIMARY KEY (messagedataid),
																				KEY messagedata1 (messageid)
																			);";

																			
				$_SWIFT["query"]["create"]["visitorbans"] = "CREATE TABLE ". TABLE_PREFIX ."visitorbans (
																				visitorbanid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				ipaddress VARCHAR(50) NOT NULL DEFAULT '0.0.0.0',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				PRIMARY KEY (visitorbanid),
																				KEY visitorbans1 (ipaddress),
																				KEY visitorbans2 (staffid),
																				UNIQUE KEY (ipaddress)
																			);";
																			
				$_SWIFT["query"]["create"]["visitornotes"] = "CREATE TABLE ". TABLE_PREFIX ."visitornotes (
																				visitornoteid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				ipaddress VARCHAR(50) NOT NULL DEFAULT '0.0.0.0',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				staffname VARCHAR(150) NOT NULL DEFAULT '',
																				PRIMARY KEY (visitornoteid),
																				KEY visitornotes1 (ipaddress),
																				KEY visitornotes2 (staffid)
																			);";
																			
				$_SWIFT["query"]["create"]["visitornotedata"] = "CREATE TABLE ". TABLE_PREFIX ."visitornotedata (
																				visitornotedataid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				visitornoteid INT UNSIGNED NOT NULL DEFAULT '0',
																				contents MEDIUMTEXT,
																				PRIMARY KEY (visitornotedataid),
																				KEY visitornotedata1 (visitornoteid)
																			);";
																			
				$_SWIFT["query"]["create"]["cannedcategories"] = "CREATE TABLE ". TABLE_PREFIX ."cannedcategories (
																				cannedcategoryid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				parentcategoryid INT UNSIGNED NOT NULL DEFAULT '0',
																				categorytype SMALLINT(1) UNSIGNED NOT NULL DEFAULT '0',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				title VARCHAR(255) NOT NULL DEFAULT '',
																				PRIMARY KEY (cannedcategoryid),
																				KEY cannedcategories1 (parentcategoryid),
																				KEY cannedcategories2 (categorytype, staffid)
																			);";
																			
				$_SWIFT["query"]["create"]["cannedresponses"] = "CREATE TABLE ". TABLE_PREFIX ."cannedresponses (
																				cannedresponseid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				cannedcategoryid INT UNSIGNED NOT NULL DEFAULT '0',
																				staffid INT UNSIGNED NOT NULL DEFAULT '0',
																				title VARCHAR(255) NOT NULL DEFAULT '',
																				responsetype VARCHAR(20) NOT NULL DEFAULT '',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				PRIMARY KEY (cannedresponseid),
																				KEY cannedresponses1 (cannedcategoryid),
																				KEY cannedresponses2 (staffid)
																			);";
																			
				$_SWIFT["query"]["create"]["cannedresponsedata"] = "CREATE TABLE ". TABLE_PREFIX ."cannedresponsedata (
																				cannedresponsedataid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				cannedresponseid INT UNSIGNED NOT NULL DEFAULT '0',
																				contents MEDIUMTEXT,
																				PRIMARY KEY (cannedresponsedataid),
																				KEY cannedresponsesdata1 (cannedresponseid)
																			);";
																			
				$_SWIFT["query"]["create"]["adcampaigns"] = "CREATE TABLE ". TABLE_PREFIX ."adcampaigns (
																				campaignid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				title VARCHAR(255) NOT NULL DEFAULT '',
																				redirecturl VARCHAR(255) NOT NULL DEFAULT '',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				PRIMARY KEY (campaignid)
																			);";
																			
				$_SWIFT["query"]["create"]["adhitlogs"] = "CREATE TABLE ". TABLE_PREFIX ."adhitlogs (
																				hitlogid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				campaignid INT UNSIGNED NOT NULL DEFAULT '0',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				referrer VARCHAR(255) NOT NULL DEFAULT '',
																				browser VARCHAR(255) NOT NULL DEFAULT '',
																				platform VARCHAR(255) NOT NULL DEFAULT '',
																				ipaddress VARCHAR(32) NOT NULL DEFAULT '',
																				PRIMARY KEY (hitlogid),
																				KEY adhitlogs1 (campaignid),
																				KEY adhitlogs2 (dateline)
																			);";
																			
				$_SWIFT["query"]["create"]["adsearchqueries"] = "CREATE TABLE ". TABLE_PREFIX ."adsearchqueries (
																				searchqueryid INT UNSIGNED NOT NULL AUTO_INCREMENT,
																				campaignid INT UNSIGNED NOT NULL DEFAULT '0',
																				dateline INT UNSIGNED NOT NULL DEFAULT '0',
																				enginename VARCHAR(255) NOT NULL DEFAULT '',
																				searchquery VARCHAR(255) NOT NULL DEFAULT '',
																				PRIMARY KEY (searchqueryid)
																			);";

				// $_SWIFT["query"]["alter"]["visitorfootprints"][0] = "ALTER TABLE `". TABLE_PREFIX ."visitorfootprints` TYPE = HEAP;";
				$_SWIFT["query"]["alter"]["visitorfootprints"][0] = "ALTER TABLE `". TABLE_PREFIX ."visitorfootprints` ENGINE = MEMORY;";
				// $_SWIFT["query"]["alter"]["visitorpulls"][0] = "ALTER TABLE `". TABLE_PREFIX ."visitorpulls` TYPE = HEAP;";
				$_SWIFT["query"]["alter"]["visitorpulls"][0] = "ALTER TABLE `". TABLE_PREFIX ."visitorpulls` ENGINE = MEMORY;";
				// $_SWIFT["query"]["alter"]["chatchilds"][0] = "ALTER TABLE `". TABLE_PREFIX ."chatchilds` TYPE = HEAP;";
				$_SWIFT["query"]["alter"]["chatchilds"][0] = "ALTER TABLE `". TABLE_PREFIX ."chatchilds` ENGINE = MEMORY;";

				// Register the module
				$_SWIFT["query"]["insert"]["settings"][0] = "INSERT INTO `". TABLE_PREFIX ."settings`(`section`, `vkey`, `data`) VALUES('registeredmodules', '". MODULE_LIVESUPPORT ."', '1')";

				break;

				case '2':
					loadIPFromCSV("./setup/includes/ip2country.1.csv");
					break;
				case '3':
					loadIPFromCSV("./setup/includes/ip2country.2.csv");
					break;
		}
	}
}


/**
* Inserts the IP To Country Data from the CSV files, to be called only during setup.
*/
function loadIPFromCSV($filename)
{
	global $_SWIFT;

	$data = file($filename);
	$cnt=0;
	for ($ii=0; $ii<count($data); $ii++) {
		$cnt++;
		$linedata = explode(",", $data[$ii]);

		if ($cnt >= 100 || ($ii+1) == count($data))
		{
			$cnt = 0;
			$queryadd .= ",('". addslashes(str_replace("\"", "",$linedata[0])) ."', '". addslashes(str_replace("\"", "",$linedata[1])) ."', '". addslashes(str_replace("\"", "",$linedata[2])) ."', '". addslashes(str_replace("\"", "",$linedata[3])) ."', '". addslashes(trim(str_replace("\"", "",$linedata[4]))) ."')";
			$_SWIFT["query"]["insert"]["countryinfo"][] = "INSERT INTO `". TABLE_PREFIX ."countryinfo` (`ip_from`, `ip_to`, `country_code2`, `country_code3`, `country_name`) VALUES".substr($queryadd, 1, strlen($queryadd));
			//(ceil($ii/100)-1)
			$queryadd = "";
		} else {
			$queryadd .= ",('". addslashes(str_replace("\"", "",$linedata[0])) ."', '". addslashes(str_replace("\"", "",$linedata[1])) ."', '". addslashes(str_replace("\"", "",$linedata[2])) ."', '". addslashes(str_replace("\"", "",$linedata[3])) ."', '". addslashes(ucwords(strtolower(trim(str_replace("\"", "",$linedata[4]))))) ."')";				
		}
	}
	if ($queryadd != "")
	{
		$_SWIFT["query"]["insert"]["countryinfo"][] = "INSERT INTO `". TABLE_PREFIX ."countryinfo` (`ip_from`, `ip_to`, `country_code2`, `country_code3`, `country_name`) VALUES".substr($queryadd, 1, strlen($queryadd));
	}
}

/**
* Uninstall Routine
*/
function _livesupport_uninstall()
{
	global $_SWIFT;

	$_SWIFT["query"]["drop"]["visitorfootprints"] = "DROP TABLE `". TABLE_PREFIX ."visitorfootprints`;";

	$_SWIFT["query"]["drop"]["visitorpulls"] = "DROP TABLE `". TABLE_PREFIX ."visitorpulls`;";

	$_SWIFT["query"]["drop"]["countryinfo"] = "DROP TABLE `". TABLE_PREFIX ."countryinfo`;";

	$_SWIFT["query"]["drop"]["chatobjects"] = "DROP TABLE `". TABLE_PREFIX ."chatobjects`;";

	$_SWIFT["query"]["drop"]["chatdata"] = "DROP TABLE `". TABLE_PREFIX ."chatdata`;";

	$_SWIFT["query"]["drop"]["chatchilds"] = "DROP TABLE `". TABLE_PREFIX ."chatchilds`;";

	$_SWIFT["query"]["drop"]["messagequeue"] = "DROP TABLE `". TABLE_PREFIX ."messagequeue`;";

	$_SWIFT["query"]["drop"]["messages"] = "DROP TABLE `". TABLE_PREFIX ."messages`;";

	$_SWIFT["query"]["drop"]["messagedata"] = "DROP TABLE `". TABLE_PREFIX ."messagedata`;";

	$_SWIFT["query"]["drop"]["visitorbans"] = "DROP TABLE `". TABLE_PREFIX ."visitorbans`;";

	$_SWIFT["query"]["drop"]["visitornotes"] = "DROP TABLE `". TABLE_PREFIX ."visitornotes`;";

	$_SWIFT["query"]["drop"]["visitornotedata"] = "DROP TABLE `". TABLE_PREFIX ."visitornotedata`;";

	$_SWIFT["query"]["drop"]["cannedcategories"] = "DROP TABLE `". TABLE_PREFIX ."cannedcategories`;";

	$_SWIFT["query"]["drop"]["cannedresponses"] = "DROP TABLE `". TABLE_PREFIX ."cannedresponses`;";

	$_SWIFT["query"]["drop"]["cannedresponsedata"] = "DROP TABLE `". TABLE_PREFIX ."cannedresponsedata`;";

	$_SWIFT["query"]["drop"]["adcampaigns"] = "DROP TABLE `". TABLE_PREFIX ."adcampaigns`;";

	$_SWIFT["query"]["drop"]["adhitlogs"] = "DROP TABLE `". TABLE_PREFIX ."adhitlogs`;";

	$_SWIFT["query"]["drop"]["adsearchqueries"] = "DROP TABLE `". TABLE_PREFIX ."adsearchqueries`;";

	$_SWIFT["query"]["delete"]["settings"][0] = "DELETE FROM `". TABLE_PREFIX ."settings` WHERE `section` = 'registeredmodules' AND `vkey` = '". MODULE_LIVESUPPORT ."';";
}
?>